import json
import os

from PyQt5.QtCore import QThread, pyqtSignal, Qt
from PyQt5.QtWidgets import QTableWidgetItem, QMessageBox

from control.Databasemanage import DatabaseManager
from test_api import getdir
from socre.score_plus import process_file

db_manager = DatabaseManager()
db_manager.connect()
cursor = db_manager.connection.cursor()

class ScoringThread(QThread):
    """用于处理阅卷任务的线程"""
    scoring_done_signal = pyqtSignal(int, str)  # 信号传递行号

    def __init__(self, subject_code, answer_qr_code, student_id, row, mainwindows, parent=None):
        super().__init__(parent)
        self.subject_code = subject_code
        self.answer_qr_code = answer_qr_code
        self.student_id = student_id
        self.row = row
        self.message = "None"
        self.mainwindows = mainwindows

    def run(self):
        """线程运行时执行的函数"""
        # 提取第一位和第二位
        question_type = self.answer_qr_code.split("、")[1]  # 第一位代表题目类型
        section_number = self.answer_qr_code.split("、")[0]  # 第二位代表大题号
        if question_type == "单选题":
            question_type = "1"
        elif question_type == "多选题":
            question_type = "2"
        elif question_type == "判断题":
            question_type = "3"

        # 转换第二位为中文大写数字
        converted_section_number = self.convert_chinese_to_arabic(section_number)
        # 组合新的值
        new_value = f"{question_type}{converted_section_number}"

        # 使用参数化查询
        query = "SELECT file_url_local FROM exam_scan_split WHERE subject_code = ? AND answer_qr_code = ? AND student_id = ?"
        cursor.execute(query, (self.subject_code, new_value, self.student_id))
        # 获取查询结果
        result = cursor.fetchone()
        # 读取设置文件
        settings_file = getdir() + 'api/ConfigJson.txt'
        with open(settings_file, 'r', encoding='utf-8') as file:
            settings = json.load(file)
        file_path = []
        # 确保 settings 中有 "filepath" 键
        if "filepath" in settings:
            filepath_prefix = settings["filepath"]
            # 假设 result 是一个包含文件 URL 的元组
            if result:
                file_url_local = result[0]  # 假设文件 URL 在元组的第一个位置
                # 构造新的文件路径
                new_file_path = os.path.join(filepath_prefix, file_url_local)
                # 这里可以将新的文件路径用于其他操作，例如显示在界面上或进行其他处理
            else:
                print("No result found.")
        else:
            print("'filepath' key not found in settings.")

        cursor.execute("SELECT paper_id FROM exam_answer_paper WHERE subject_code = ?", (self.subject_code,))
        # 获取查询结果
        paper_id_result = cursor.fetchone()

        if paper_id_result:
            paper_id = paper_id_result[0]  # 提取 paper_id

            cursor.execute(
                "SELECT answer_id, answer_no, answer_score, parent_answer_id FROM exam_answer_into WHERE paper_id = ? AND answer_qr_code = ?",
                (paper_id, new_value))
            answer_id_result = cursor.fetchall()  # 获取查询结果

            # cursor.execute("SELECT answer_id, answer_standard FROM exam_answer_into WHERE paper_id = ? AND answer_id = ?",(paper_id, answer_id))
            # 创建一个字典来存储 answer_id 和 answer_standard 的对应关系
            answer_standards = {}

            # 遍历 data 列表，查询每个 answer_id 对应的 answer_standard
            for answer_id, _, _, _ in answer_id_result:
                cursor.execute("SELECT answer_standard FROM exam_answer_standard WHERE paper_id = ? AND answer_id = ?",
                               (paper_id, answer_id))
                result = cursor.fetchone()

                if result:
                    # 将查询结果存储在字典中
                    answer_standards[answer_id] = result.answer_standard
                else:
                    print(f"No data found for answer_id: {answer_id}")

            # 创建一个新的列表，包含更新后的元组
            updated_data = []
            for answer_id, tid, score, parent_answer_id in answer_id_result:
                answer_standard = answer_standards.get(answer_id, None)  # 从字典中获取 answer_standard
                updated_data.append(
                    (paper_id, tid, score, answer_id, answer_standard, parent_answer_id))  # 创建新的元组并添加到列表中

            tid_to_standard = {str(tid): f"{answer_standard}-{score}-{answer_id}-{score}-{paper_id}-{parent_answer_id}"
                               for paperid, tid, score, answer_id, answer_standard, parent_answer_id in updated_data}
            print(f"tid_sanrand:{tid_to_standard}")
            # # 打印结果
            # print(updated_data)
            correct_answers = list(tid_to_standard.values())

            # print(correct_answers)

            score_1 = [(i + 1, float(answer.split('-')[1])) for i, answer in enumerate(correct_answers)]
            score_2 = [(i + 1, int(float(answer.split('-')[2]))) for i, answer in enumerate(correct_answers)]
            score_3 = [(i + 1, int(float(answer.split('-')[5]))) for i, answer in enumerate(correct_answers)]
            paperId = list(tid_to_standard.values())[0].split('-')[-2]
            parent_answer_id = list(tid_to_standard.values())[0].split('-')[-1]
            correct_answers_with_index = [(score_2[i][1], score_1[i][1], answer.split('-')[0], score_3[i][1]) for
                                          i, answer in enumerate(correct_answers)]
            # print(f"score_1: {score_1}")
            # print(f"score_2: {score_2}")
            # print(f"score_3: {score_3}")
            # print(f"paperId: {paperId}")
            # print(f"correct_answers_with_index: {correct_answers_with_index}")

            parts = os.path.basename(new_file_path).split('_')
            subjectCode = parts[0]
            answerQrCode = parts[-1].split('.')[0]
            studentId = parts[2]
            # 检查文件是否已存在
            if os.path.exists(new_file_path):
                file_path = new_file_path
                # 自动评分程序#########
                result_answer = process_file(file_path, correct_answers_with_index, subjectCode, studentId, paperId,
                                             answerQrCode, parent_answer_id)

                # print(self.result_answer)
                ############
                cursor.execute(
                    "SELECT paper_no, exam_year FROM exam_subject_student WHERE paper_id = ? AND subject_code = ? AND student_id = ?",
                    (paper_id, self.subject_code, self.student_id))
                results = cursor.fetchone()
                paper_no = results[0]
                # print(self.result_answer)
                ############
                if len(result_answer) != 0 and "Fail to deal with" not in result_answer:

                    exam_year = results[1]

                    # 检查是否有任何元组的第五个元素是 None
                    contains_none = any(element.split('_')[4] == "None" for element in result_answer)

                    if contains_none:
                        self.Marking_error(exam_year, self.student_id, paper_id, self.subject_code, parent_answer_id,
                                           paper_no)
                        print("存在至少一个元组的第五个元素是 None")
                        self.message = "fail"
                    else:
                        self.Mark_correctly(exam_year, self.student_id, paper_id, self.subject_code, parent_answer_id,
                                            paper_no)
                        print("没有任何元组的第五个元素是 None")
                        # 查询阅卷状态
                        query = "SELECT split_answer_status FROM exam_scan_split WHERE subject_code = ? AND answer_qr_code = ? AND student_id = ?"
                        cursor.execute(query, (self.subject_code, new_value, self.student_id))
                        # 获取查询结果
                        res = cursor.fetchone()
                        result_status = res[0]
                        print(self.subject_code, new_value, self.student_id, result_status, self.row, '=--=-=-=-=-=-')
                        if result_status == 1:
                            self.message = "success"

                            # # 更新考生得分
                            # sql_score = "SELECT subject_code, paper_id, student_id, answer_id, parent_answer_id, answer_standard, answer_student, answer_score FROM exam_scan_split_answer WHERE student_id = ? "
                            # cursor.execute(sql_score, student_id)
                            # studentscore_dic = cursor.fetchall()
                            # print(studentscore_dic,"========================")
                            # scores_dict = self.calculate_scores(studentscore_dic)
                            # print(scores_dict)

                    for answer_score in result_answer:
                        answer_id = answer_score.split('_')[0]
                        subject_code = answer_score.split('_')[1]
                        student_id = answer_score.split('_')[2]
                        answer_standards = answer_score.split('_')[3]
                        answer_student = answer_score.split('_')[4]
                        paper_id = answer_score.split('_')[5]
                        question_answer_score = answer_score.split('_')[6]
                        parent_answer_id = answer_score.split('_')[7]
                        summary_status = 0

                        values = (
                        subject_code, paper_id, student_id, paper_no, answer_id, parent_answer_id, answer_standards,
                        answer_student, exam_year, question_answer_score, summary_status)

                        try:
                            # 执行MERGE操作，尝试插入，如果违反唯一约束则更新answer_student字段
                            cursor.execute("""
                                   MERGE INTO exam_scan_split_answer AS target
                                   USING (VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)) AS source
                                   (subject_code, paper_id, student_id, paper_no, answer_id, parent_answer_id, answer_standard, answer_student, exam_year, answer_score, summary_status)
                                   ON target.subject_code = source.subject_code
                                       AND target.paper_id = source.paper_id
                                       AND target.student_id = source.student_id
                                       AND target.paper_no = source.paper_no
                                       AND target.answer_id = source.answer_id
                                       AND target.parent_answer_id = source.parent_answer_id
                                   WHEN NOT MATCHED THEN
                                       INSERT (subject_code, paper_id, student_id, paper_no, answer_id, parent_answer_id, answer_standard, answer_student, exam_year, answer_score, summary_status)
                                       VALUES (source.subject_code, source.paper_id, source.student_id, source.paper_no, source.answer_id, source.parent_answer_id, source.answer_standard, source.answer_student, source.exam_year, source.answer_score, source.summary_status)
                                   WHEN MATCHED THEN
                                       UPDATE SET 
                                       target.answer_student = source.answer_student,
                                       target.summary_status = source.summary_status,     
                                       target.answer_score = source.answer_score;
                               """, values)
                            # 提交事务
                            db_manager.connection.commit()
                        except Exception as e:
                            # 打印错误信息
                            print("Error occurred Thread:", e)
                            # 回滚事务
                            db_manager.connection.rollback()
                    print(new_value, self.subject_code, self.student_id, "]]]]]")

                    # 步骤 1: 执行第一个 SQL 查询并获取所有的 subject_code
                    cursor.execute(
                        "SELECT answer_id FROM exam_scan_split WHERE answer_qr_code = ? and subject_code = ? and student_id = ?",
                        (new_value, self.subject_code, self.student_id))
                    answerid_row = cursor.fetchall()
                    print(answerid_row)
                    parent_answer_id_plus = answerid_row[0][0]
                    # 更新考生得分
                    sql_score = "SELECT subject_code, paper_id, student_id, answer_id, parent_answer_id, answer_standard, answer_student, answer_score FROM exam_scan_split_answer WHERE student_id = ? and parent_answer_id = ? "
                    cursor.execute(sql_score, self.student_id, parent_answer_id_plus)
                    studentscore_dic = cursor.fetchall()
                    scores_dict = self.calculate_scores(studentscore_dic)
                    print(scores_dict)
                    # 更新題目分数
                    for i, record in enumerate(scores_dict):
                        total_score = record.get('total_score', 0)  # 获取 total_score 的值，如果没有则默认为 0
                        item = QTableWidgetItem(str(total_score))  # 将 total_score 转换为字符串
                        item.setTextAlignment(Qt.AlignCenter | Qt.AlignVCenter)  # 水平和垂直居中
                        item.setFlags(item.flags() & ~Qt.ItemIsEditable)  # 设置为不可编辑
                        self.mainwindows.tableWidget_2.setItem(self.row, 9, item)  # 设置到第 9 列

                    self.scoring_done_signal.emit(self.row, self.message)

                else:
                    name1 = os.path.basename(file_path)
                    name, _ = os.path.splitext(name1)
                    part = name.split('_')
                    exam_year = part[1]
                    self.Marking_error(exam_year, self.student_id, paper_id, self.subject_code, parent_answer_id,
                                       paper_no)
        else:
            QMessageBox.information(self, "错误", "本地没有该考生图片")

    def convert_chinese_to_arabic(self, chinese_number):
        """将中文数字转换为阿拉伯数字"""
        chinese_to_arabic = {
            '一': '1', '二': '2', '三': '3', '四': '4',
            '五': '5', '六': '6', '七': '7', '八': '8',
            '九': '9', '十': '10'
        }
        return chinese_to_arabic.get(chinese_number, '0')

    def Marking_error(self, exam_year, student_id, paper_id, subject_code, parent_answer_id, paper_no):
        """标记为阅卷错误"""
        # 根据给定的字段更新split_answer_status为2
        cursor.execute("""
               UPDATE exam_scan_split
               SET split_answer_status = 2
               WHERE exam_year = ? AND student_id = ? AND paper_id = ? AND subject_code = ? AND answer_id = ?
           """, (exam_year, student_id, paper_id, subject_code, parent_answer_id))
        # 提交事务
        db_manager.connection.commit()

        # 根据给定的字段更新split_answer_status为2
        cursor.execute("""
               UPDATE exam_subject_paper_initialize
               SET split_answer_check_status = 2
               WHERE exam_year = ? AND student_id = ? AND paper_id = ? AND subject_code = ? AND answer_id = ? AND paper_no = ?
           """, (exam_year, student_id, paper_id, subject_code, parent_answer_id, paper_no))
        # 提交事务
        db_manager.connection.commit()

    def Mark_correctly(self, exam_year, student_id, paper_id, subject_code, parent_answer_id, paper_no):
        """标记为阅卷正确"""
        # 根据给定的字段更新split_answer_status为1
        cursor.execute("""
               UPDATE exam_scan_split
               SET split_answer_status = 1
               WHERE exam_year = ? AND student_id = ? AND paper_id = ? AND subject_code = ? AND answer_id = ?
           """, (exam_year, student_id, paper_id, subject_code, parent_answer_id))
        # 提交事务
        db_manager.connection.commit()

        # 根据给定的字段更新split_answer_status为1
        cursor.execute("""
               UPDATE exam_subject_paper_initialize
               SET split_answer_check_status = 1
               WHERE exam_year = ? AND student_id = ? AND paper_id = ? AND subject_code = ? AND answer_id = ? AND paper_no = ?
           """, (exam_year, student_id, paper_id, subject_code, parent_answer_id, paper_no))
        # 提交事务
        db_manager.connection.commit()

    def calculate_scores(self, answers):
        """计算得分"""
        # 初始化一个字典来存储每个大题的得分情况
        score_dict = {}

        # 遍历答题数据
        for subject_code, exam_number, candidate_number, question_group, question_number, correct_answer, student_answer, score in answers:
            # 确保 score 是整数
            if score is not None:
                score = int(score)

            # 创建一个唯一的键来标识每个大题
            key = (subject_code, exam_number, question_number, candidate_number)

            # 如果大题不在字典中，初始化它的得分
            if key not in score_dict:
                score_dict[key] = {
                    'paper_id': exam_number,
                    'subject_code': subject_code,
                    'answer_id': question_number,
                    'student_id': candidate_number,
                    'total_score': 0
                }

            # 如果学生答案正确，则累加分数
            if student_answer == correct_answer and isinstance(score, int):
                score_dict[key]['total_score'] += score

        # 将字典的值转换为列表
        return list(score_dict.values())